#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
insert into hive.zx_dm.dm_apply_theme
with temp1 as (
    select
           substring(create_date, 1, 4) as year_code,
           substring(create_date, 6, 2) as month_code,
           substring(create_date, 9, 2) as day_code,

           school_id,
           school_name,
           origin_type,
           subject_id,
           subject_name,
           origin_channel,
           department_id,
           department_name,
           apply_count,
           intention_count,
           valid_thread_count,
           group_mark                   as old_group_mark,
           dt
    from hive.zx_dws.dws_apply_theme
	where dt = '${TD_DATE}'),
     temp2 as (
         select
                case
                    when grouping(year_code, month_code, day_code) = 0 then 'day'
                    when grouping(year_code, month_code) = 0 then 'month'
                    when grouping(year_code) = 0 then 'year'
                    end        as time_type,
                year_code,
                month_code,
                day_code,
                school_id,
                school_name,
                origin_type,
                subject_id,
                subject_name,
                origin_channel,
                department_id,
                department_name,
                sum (apply_count) as apply_count,
                sum (intention_count) as intention_count,
                sum(valid_thread_count) as valid_thread_count,
                old_group_mark,
                case
                    when grouping(origin_type, school_id, subject_id) = 0 then 4
                    when grouping(origin_type, subject_id) = 0 then 3
                    when grouping(origin_type, school_id) = 0 then 2
                    when grouping(school_id) = 0 then 1
                    when grouping(origin_type, origin_channel) = 0 then 5
                    when grouping(origin_type, department_id) = 0 then 6
                    when grouping(origin_type) = 0 then 7
                    end        as new_group_mark,
                max(dt) as dt
         from temp1
         group by
             grouping sets (
             -- 年份
             (year_code, school_id, school_name, old_group_mark),
             (year_code, origin_type, school_id, school_name, old_group_mark),
             (year_code, origin_type, subject_id, subject_name, old_group_mark),
             (year_code, origin_type, school_id, school_name, subject_id, subject_name, old_group_mark),
             (year_code, origin_type, origin_channel, old_group_mark),
             (year_code, origin_type, department_id, department_name, old_group_mark),
             (year_code, origin_type, old_group_mark),
             -- 月份
             (year_code, month_code, school_id, school_name, old_group_mark),
             (year_code, month_code, origin_type, school_id, school_name, old_group_mark),
             (year_code, month_code, origin_type, subject_id, subject_name, old_group_mark),
             (year_code, month_code, origin_type, school_id, school_name, subject_id, subject_name, old_group_mark),
             (year_code, month_code, origin_type, origin_channel, old_group_mark),
             (year_code, month_code, origin_type, department_id, department_name, old_group_mark),
             (year_code, month_code, origin_type, old_group_mark),
             -- 日
             (year_code, month_code, day_code, school_id, school_name, old_group_mark),
             (year_code, month_code, day_code, origin_type, school_id, school_name, old_group_mark),
             (year_code, month_code, day_code, origin_type, subject_id, subject_name, old_group_mark),
             (year_code, month_code, day_code, origin_type, school_id, school_name, subject_id, subject_name,
              old_group_mark),
             (year_code, month_code, day_code, origin_type, origin_channel, old_group_mark),
             (year_code, month_code, day_code, origin_type, department_id, department_name, old_group_mark),
             (year_code, month_code, day_code, origin_type, old_group_mark)
             )
     )
select
       time_type,
       year_code,
       month_code,
       day_code,
       school_id,
       school_name,
       origin_type,
       subject_id,
       subject_name,
       origin_channel,
       department_id,
       department_name,
       apply_count,
       intention_count,
       valid_thread_count,
       new_group_mark as group_mark,
       dt
from temp2
where new_group_mark = old_group_mark;"